﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Dapper.Extensions;
using Entity;
using Newtonsoft.Json;
using RestSharp;

namespace YouWei.Console
{
    /// <summary>
    /// 获取车型json
    /// </summary>
    public class CarService
    {
        static IDbConnection db = new SqlConnection("Data Source=192.168.1.221; User ID=developer; Password=developer; Initial Catalog=YCCar");
        static IDbConnection db1 = new SqlConnection("Data Source=39.106.117.1,1355;Initial Catalog=YouWei;User ID=CheGuan_Live;Password=CService_@ad!m@");

        public static void processCarBrand()
        {
            //查询brandid=0的主品牌id
            string sql = @"SELECT distinct MasterBrandID  FROM [CarSerial] where BrandID=0";
            List<int> mbids = db.Query<int>(sql).AsList();

            foreach (var mbid in mbids)
            {
                var mbsql = " select * from CarMasterBrand WHERE MasterBrandID=" + mbid;
                var mb = db.Query<CarMasterBrand>(mbsql).FirstOrDefault();
                if (mb != null)
                {
                    //把master表的name和主品牌id存入brand表
                    var bentity = new CarBrand()
                    {
                        BrandName = mb.MasterBrandName,
                        MasterBrandID = mbid,
                        BrandID = 1000000 + mbid
                    };
                    //var count = db.Query<int>("select count(1) from CarBrand").FirstOrDefault();
                    ////生成一条新的brand，表没自增，手动自增
                    //bentity.BrandID = count + 1;
                    db.Insert<CarBrand>(bentity);
                    db.Execute("update [YCCar].[dbo].[CarSerial] set BrandID=" + bentity.BrandID + "where MasterBrandID=" + mbid);
                }                
            }
        }
        public static string getJosn()
        {

            //查询主品牌
            string sql = @"SELECT  [MasterBrandName] AS [text],
                                   [Url] AS [url],
                                   [MasterBrandID] AS [value],
                                   [Capital] AS [capital]
                           FROM    [YCCar].[dbo].[CarMasterBrand]";
            List<mastbrand> mbs = db.Query<mastbrand>(sql).AsList();

            //查询品牌
            string sql1 = @"SELECT  BrandName AS [text],                              
                                   BrandID AS [value],
                                   MasterBrandID as pid
                           FROM    [YCCar].[dbo].[CarBrand]";
            List<brand> bs = db.Query<brand>(sql1).AsList();

            //查询车型
            string sql2 = @"SELECT  SerialName AS [text],
                                   '' AS [url],
                                   SerialID AS [value],
                             BrandID AS [pid]
                           FROM    [YCCar].[dbo].CarSerial
                            ORDER BY SerialName ASC";
            List<seiral> ss = db.Query<seiral>(sql2).AsList();

            foreach (var brand in bs)
            {
                brand.child = ss.Where(t => t.pid == brand.value).ToList();
            }

            foreach (var mastbrand in mbs)
            {
                mastbrand.url = "m_" + mastbrand.value + "_100.png";
                mastbrand.child = bs.Where(t => t.pid == mastbrand.value).ToList();
            }
            return JsonConvert.SerializeObject(mbs);
        }

        /// <summary>
        /// 添加差量
        /// </summary>
        public static void AddDiff()
        {
            //List<CarMasterBrand> mbs = db.Query<CarMasterBrand>("SELECT * FROM CarMasterBrand").AsList();
            //List<CarMasterBrand> mbs1 = db1.Query<CarMasterBrand>("SELECT * FROM CarMasterBrand").AsList();
            //foreach (var item in mbs)
            //{
            //    if (!mbs1.Exists(t => t.MasterBrandID == item.MasterBrandID))
            //    {
            //        db1.Insert<CarMasterBrand>(item);
            //    }
            //}
            //List<CarBrand> mbs = db.Query<CarBrand>("SELECT * FROM CarBrand").AsList();
            //List<CarBrand> mbs1 = db1.Query<CarBrand>("SELECT * FROM CarBrand").AsList();
            //foreach (var item in mbs)
            //{
            //    if (!mbs1.Exists(t => t.BrandID == item.BrandID))
            //    {
            //        db1.Insert<CarBrand>(item);
            //    }
            //}
            List<CarSerial> mbs = db.Query<CarSerial>("SELECT * FROM CarSerial").AsList();
            List<CarSerial> mbs1 = db1.Query<CarSerial>("SELECT * FROM CarSerial").AsList();
            foreach (var item in mbs)
            {
                if (!mbs1.Exists(t => t.SerialID == item.SerialID))
                {
                    db1.Insert<CarSerial>(item);
                }
            }
        }

        //public static string loadFile(string )
        //{
        //    ///autoalbum/files/20190606/645/0643266458_1.png
        //     RestClient client = new RestClient("http://img1.bitautoimg.com");
        //    var request = new RestRequest("", Method.GET);
        //    IRestResponse response = client.Execute(request);
        //}
    }

    public class brand
    {
        public string value { set; get; }

        public string text { set; get; }

        public List<seiral> child { set; get; }

        [JsonIgnoreAttribute]
        public string pid { set; get; }
    }

    public class seiral
    {
        public string value { set; get; }

        public string url { set; get; }

        public string text { set; get; }

        [JsonIgnoreAttribute]
        public string pid { set; get; }
    }

    public class mastbrand
    {
        public string value { set; get; }

        public string url { set; get; }

        public string capital { set; get; }

        public string text { set; get; }

        public List<brand> child { set; get; }
    }
}
